﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;

namespace BLL
{
   public class ClsOngLocBus
    {
        public ClsOngLocInfo Info = new ClsOngLocInfo();
        public DataTable TimKiemGK()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.TenGK,OngChongLoc.MaLop,OngChongLoc.ChieuSau,OngChongLoc.DKTrong,OngChongLoc.DKNgoai,OngChongLoc.VLLamOng,OngChongLoc.KichThuocLoOng,OngChongLoc.MoTa,LoaiOngLoc.TenOngLoc
                              FROM TTCHUNG, OngChongLoc,LoaiOngLoc
                              WHERE TTCHUNG.MaGK= OngChongLoc.MaGK 
                              AND  LoaiOngLoc.MaOngLoc=OngChongLoc.LoaiOngLoc
                              AND TTCHUNG.MaGK like'%" + Info.MaGK +
                             "%'AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "%'";

            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }

        public DataTable SelectAll()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT TTCHUNG.TenGK,OngChongLoc.MaLop,OngChongLoc.ChieuSau,OngChongLoc.DKTrong,OngChongLoc.DKNgoai,OngChongLoc.VLLamOng,OngChongLoc.KichThuocLoOng,OngChongLoc.MoTa,LoaiOngLoc.TenOngLoc
                              FROM TTCHUNG, OngChongLoc,LoaiOngLoc
                              WHERE TTCHUNG.MaGK= OngChongLoc.MaGK 
                              AND  LoaiOngLoc.MaOngLoc=OngChongLoc.LoaiOngLoc
                              AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "%'";                          
            //string strSql = @"SELECT * From ThachHoc";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }

        public DataTable LoadGK()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT MaGK,TenGK FROM TTCHUNG WHERE AND TTCHUNG.Tinh LIKE '%" + Info.MaTinh + "%'";
            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else
            {
                return null;
            }
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertOngChongLoc'" + Info.MaGK + "','" + Info.MaLop + "','" + Info.ChieuSau + "','" + Info.DKTrong + "','" + Info.DKNgoai + "',N'" + Info.VLLamOng + "','" + Info.KichThuocLoOng + "','" + Info.LoaiOngLoc + "',N'" + Info.MoTa + "'";

            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateOngChongLoc'" + Info.MaGK + "','" + Info.MaLop + "','" + Info.ChieuSau + "','" + Info.DKTrong + "','" + Info.DKNgoai + "',N'" + Info.VLLamOng + "','" + Info.KichThuocLoOng + "','" + Info.LoaiOngLoc + "',N'" + Info.MoTa + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
        public bool xoa()
        {
            string strSql = "EXECUTE DeleteOngChongLoc'" + Info.MaGK + "','" + Info.MaLop + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }
    }
}
